import { getFileBaseNameOnly } from '@/pages/AuditPaper/PaperTable/components/BatReplace/fileUtils';
import { msgError, msgInfo } from '@/utils/msg';
import {
  formatDateTimeForTableName,
  getGuid,
  isJSONStr,
  isNumeric,
  replaceInvalidFileName,
} from '@/utils/pub';
import { IColumnProps } from 'devextreme-react/data-grid';
import XLSX, { ColInfo, ParsingOptions, Range, WorkBook } from 'xlsx';
import { consts } from '../consts';
import { ExcelField, ExcelTable } from '../typing';

/***
 * 获取dataGrid列
 */

export function getExcelSourceColums(excelSourceFields: ExcelField[]): IColumnProps[] {
  if (excelSourceFields.length == 0) return [];
  return [
    {
      dataField: 'rn',
      caption: consts.xh,
      alignment: 'center',
      width: 60,
    },
  ].concat(
    excelSourceFields.map((item) => {
      return {
        dataField: item.fieldName,
        caption: item.fieldTitle,
        alignment: item.align,
        width: item.displayWidth,
        format: item.displayFormat,
        visible: item.enabled,
        allowFiltering: item.enabled,
        dataType: item.isNum ? 'number' : undefined,
      };
    }),
  );
}

/**
 * 文件名支持${}参数替换
 * @param localFileName
 * @returns
 */
export async function downLocalFileArrayBuffer(localFileName: string): Promise<ArrayBuffer> {
  let mainObject = window['main'].mainObject;
  //交换文件名 本地是中文,由英文改为中文
  let platform_cpasApiUrl = mainObject.getWebAppUserConfig().cpasApiUrl; //平台的cpasApiUrl
  let platform_cpasToken = mainObject.getWebAppUserConfig().cpasMainToken;

  let platform_url = platform_cpasApiUrl + '/ac/downloadFile';
  const requestData: RequestInit = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json;charset=UTF-8',
      cpasToken: platform_cpasToken,
    },
    body: JSON.stringify({
      fileFullName: localFileName,
      //displayFileName: excelFile.fileName,
    }),
  };
  const arrayBuffer: ArrayBuffer = await (await fetch(platform_url, requestData)).arrayBuffer();
  return arrayBuffer;
}

//获取解析的选项
export function getParsingOptions(
  sheets?: number | string | Array<number | string>,
): ParsingOptions {
  let opts: ParsingOptions = {
    sheets: sheets, //空数组的sheets表示不读取内容
    cellHTML: false,
    cellFormula: false,
    cellStyles: true, //20230624日期格式 yyyy-mm-dd 或 yyyy/mm/dd
    bookFiles: false,
    bookVBA: false,
    // bookDeps: false, //会慢
    // bookSheets: false,
    cellText: true,
    WTF: true, //报错
    // sheetRows: 20,
  };
  return opts;
}

export function getWorkSheets(arrayBuffer: ArrayBuffer, hiddenValue: 0 | 1 | 2): string[] {
  let opts = getParsingOptions(); //空数组的sheets表示不读取内容
  opts.bookSheets = true; //只解析 SheetNames
  let wb: WorkBook = XLSX.read(arrayBuffer, opts);
  return wb.SheetNames;
}
/**
 * 获取样本表
 * @returns
 */
export const getExcelTableList = (
  arrayBuffer: ArrayBuffer,
  maxOrdexIndex: number,
  excelFilePath: string,
): ExcelTable[] => {
  let workSheetNames = getWorkSheets(arrayBuffer, 0);
  let opts = getParsingOptions(workSheetNames);
  let wb: WorkBook = XLSX.read(arrayBuffer, opts);
  let tableSuffix = '-' + formatDateTimeForTableName(new Date());

  if (workSheetNames.length) {
    let excelTableList: ExcelTable[] = workSheetNames.map((sheetName, index) => {
      const sheet = wb.Sheets[sheetName];
      const ref = sheet['!ref'] || '';
      const range: Range = XLSX.utils.decode_range(ref); //索引号从0开始
      const totalCount = range.e.r - range.s.r + 1;
      let tableTitle = sheetName;
      //如果只有一个改个名称
      if (excelFilePath && workSheetNames.length == 1 && sheetName == 'sheet1') {
        tableTitle = replaceInvalidFileName(getFileBaseNameOnly(excelFilePath));
      }
      //sqlserve表名最长128,还需要预留一些给allSampleRecordTable,sheetName只能取80
      const tableName = `sample_imp_${tableTitle.substring(0, 80)}${tableSuffix}`;
      return {
        rn: index + 1,
        guid: index + 1 + '',
        tableName,
        tableTitle: tableTitle,
        isVoucher: false,
        isCustom: true,
        orderIndex: maxOrdexIndex + index + 1,
        sampleTextFields: '',
        sampleNumFields: '',
        sortFields: '',
        enabled: true,
        remarks: '',
        sampleTextFieldsArray: [],
        sampleNumFieldsArray: [],
        sortFieldsArray: [],
        sheet,
        range,
        dataRowCount: totalCount - 1, //数据行数据
        totalCount: totalCount,
        excelFields: [],
        tableRowsData: [],
        sampleAllFieldsArray: [],
        fieldsInfo: '',
        titleRowNo: 1,
        endRowNo: totalCount, //让用户看清行号
      } as ExcelTable;
    });
    return excelTableList;
  } else {
    return [];
  }
};

/**
 * 获取排序列
 * @param excelTable
 * @returns
 */
export function getOrderIndexExcelField(excelTable: ExcelTable): ExcelField {
  const excelField: ExcelField = {
    guid: getGuid(),
    tableName: excelTable.tableName,
    fieldName: consts.orderIndexFieldName,
    fieldTitle: consts.autoIncText, //'自动编号',
    isKey: false,
    isNum: 1,
    align: 'center',
    displayWidth: 40,
    visible: false,
    displayFormat: '',
    orderIndex: 9999, //最后一列
    enabled: true,
    remarks: '',
    textMaxLen: 0,
    fieldSql: '',
  };
  return excelField;
}
/**
 * 获取一个Excel的数据
 * @param excelTable
 * @param excelFields
 * @param titleRowNo
 */
export function getExcelTableRowsData(excelTable: ExcelTable, excelFields: ExcelField[]): any[] {
  const range = excelTable.range;
  const tableRowsData: any[] = [];
  const titleRowNo: number = excelTable.titleRowNo;
  //如果是负数的,属于表示结束到倒数第几行

  const endRowNo: number =
    excelTable.endRowNo <= 0 ? excelTable.totalCount + excelTable.endRowNo : excelTable.endRowNo;

  for (let row = 0; row <= range.e.r; row++) {
    if (row >= titleRowNo && row < endRowNo) {
      //row从0开始,titleRowNo从1开始
      const rowData: any = { rn: tableRowsData.length + 1 };
      rowData.excelRowNo = tableRowsData.push(rowData);
      for (let col = 0; col < excelFields.length; col++) {
        const excelField = excelFields[col];
        const cellAddress = XLSX.utils.encode_cell({ c: col, r: row });
        let cellValue = excelTable.sheet[cellAddress]?.v ?? null;
        /*   if (cellValue == '0' || cellValue == '0.00') {
                      cellValue = '';//零值显示为空
                  } */
        const excelSourceField = excelFields[col];
        if (!excelField.isNum) {
          cellValue = cellValue + ''; //如果非数字,转换为字符串
        }
        if (cellValue == 'null' || cellValue == 'undefined') {
          cellValue = excelField.isNum ? 0 : '';
        }
        rowData[excelSourceField.fieldName] = cellValue;
        excelField.textMaxLen = Math.max(
          excelField.textMaxLen || 0,
          cellValue?.toString().length || 0,
        );
        if (excelSourceField.fieldTitle == consts.autoIncText) {
          excelField.textMaxLen = 100;
        }
      }
      rowData[consts.orderIndexFieldName] = rowData.rn; //加序号
      rowData[consts.excelRowIndexFieldName] = row + 1; // 从1,2,3
    }
  }

  for (let col = 0; col < excelFields.length; col++) {
    const excelField = excelFields[col];
    if (excelField.isNum == 0) {
      const isNum = !tableRowsData.some((rowData) => isNaN(rowData[excelField.fieldName]));
      if (isNum) {
        excelField.isNum = isNum ? 1 : 0;
        let maxDecimalPlaces = 0;
        tableRowsData.forEach((rowData) => {
          const decimalPlaces = (rowData[excelField.fieldName].toString().split('.')[1] || '')
            .length;
          maxDecimalPlaces = Math.max(maxDecimalPlaces, decimalPlaces);
        });
        if (maxDecimalPlaces > 0) {
          excelField.align = 'right';
          excelField.displayFormat = consts.numberFormat2Bit;
        }
        if (maxDecimalPlaces > 2) {
          excelField.displayFormat = consts.numberFormat4Bit;
        }
        if (maxDecimalPlaces > 4) {
          excelField.displayFormat = consts.numberFormat6Bit;
        }
      }
    }
    if (excelField.isNum && (excelField.displayFormat || '').includes(consts.numberFormat0)) {
      tableRowsData.forEach((item) => {
        if (item[excelField.fieldName] == '0' || item[excelField.fieldName] == '0.00') {
          item[excelField.fieldName] = null;
        }
      });
    }
  }
  return tableRowsData;
}
/**
 * 获取取的格式化字符串,从标题行的下一行试读取3行,titleRowNo从1开始的
 * @param excelTable
 * @param col
 * @param titleRowNo
 * @returns
 */
export function getExcelColumnFormat(excelTable: ExcelTable, col: number, titleRowNo: number) {
  let format: string = '';
  let isNum: number = 0;
  for (let row = titleRowNo; row < titleRowNo + 3; row++) {
    const cellObj = excelTable.sheet[XLSX.utils.encode_cell({ c: col, r: row })];
    format = cellObj?.z || ''; //格式化
    if (format) {
      // console.log(col + '.format', format)

      if ((format + '').includes(consts.numberFormat0) && isNumeric(cellObj?.v || '0')) {
        format = consts.numberFormat; //默认是数字格式
        isNum = 1;
      } else {
        format = ''; //如果是数字格式,但又不是真实的数据的
      }
      break;
    }
  }
  return { format, isNum };
}
/**
 * 通过列名判读是否是数字
 * @param colName
 * @returns
 */
export function getColNameIsNotNum(colName: string) {
  return (
    typeof colName == 'string' &&
    (colName.endsWith('编号') ||
      colName.endsWith('编码') ||
      colName.endsWith('名称') ||
      colName.includes('科目编号') ||
      colName.includes('科目名称'))
  );
}
/**
 * 通过excel列号获取宽度 col从0开始
 * @param colInfos
 * @param col
 */
export function getDisplyWidth(colInfos: ColInfo[], col: number) {
  let displayWidth = 120;

  if (colInfos && colInfos[col]?.width) {
    displayWidth = (colInfos[col].width || 16) * consts.pixelsPerCharacter;
  }
  displayWidth = Number(displayWidth.toFixed(0));
  if (displayWidth < 60) displayWidth = 60;
  return displayWidth;
}
export function getFieldSql(excelField: ExcelField) {
  let fieldSql = '';
  if (excelField.isNum) {
    if (excelField.displayFormat.includes(consts.numberFormat0)) {
      fieldSql = `numeric(18,2)`;
    } else {
      fieldSql = 'int';
    }
  } else {
    //字段长度最小40
    let fieldLength: number | string = Math.max(
      50,
      excelField.textMaxLen ? excelField.textMaxLen * 3 : 200,
    );
    fieldLength = Math.ceil(fieldLength / 10) * 10; //将一个数字的个位数向上加到最接近的十位数（即个位数为0）
    fieldLength = fieldLength > 4000 ? 'max' : fieldLength;
    fieldSql = `varchar(${fieldLength})`;
  }
  return fieldSql;
}
/**
 * 获取ExcelFields
 * @param excelTable
 * @returns
 */
export function getExcelFields(excelTable: ExcelTable /* , titleRowNo: number */) {
  const excelFields: ExcelField[] = [];
  excelTable.excelFields = excelFields;
  const range = excelTable.range;
  const colInfos: ColInfo[] = excelTable.sheet['!cols'] as ColInfo[];

  for (let col = range.s.c; col <= range.e.c; col++) {
    const cellAddress = XLSX.utils.encode_cell({ c: col, r: excelTable.titleRowNo - 1 });
    let cellValue: string = (excelTable.sheet[cellAddress]?.v || `列${col + 1}`) + '';
    cellValue = cellValue.replace(/[^a-zA-Z0-9\u4e00-\u9fa5]/g, '');
    let displayWidth = getDisplyWidth(colInfos, col);
    //格式字符
    let { format, isNum } = getExcelColumnFormat(excelTable, col, excelTable.titleRowNo);
    if (getColNameIsNotNum(cellValue)) {
      isNum = 0;
      format = '';
    }
    const excelField: ExcelField = {
      guid: getGuid(),
      tableName: excelTable.tableName,
      fieldName: cellValue,
      fieldTitle: cellValue,
      isKey: false,
      isNum: isNum,
      align: isNum ? 'right' : 'left',
      displayWidth,
      visible: true,
      displayFormat: format,
      orderIndex: col + 1,
      enabled: true,
      remarks: '',
      textMaxLen: 0,
    };
    excelFields.push(excelField);
  }

  excelFields.push(getOrderIndexExcelField(excelTable)); //加第一个序号字段
  return excelFields;
}

export function updateHttpResponseProgressInfo(
  response: Response,
  setMergeProgressInfo: any, //是一个函数 {progressText: '', progressValue: 0,}=>()
  onFinished: (retData: API.SerResult) => void,
) {
  const reader = response.body?.getReader();
  const textDecoder = new TextDecoder();
  function readChunk(): any {
    return reader?.read().then(({ value, done }) => {
      const chunk = textDecoder.decode(value);
      const a_chunk = chunk.split('\n');
      const index = a_chunk.length > 1 ? a_chunk.length - 2 : a_chunk.length - 1;
      const retData = isJSONStr(a_chunk[index]) ? JSON.parse(a_chunk[index]) : {};
      console.log('updateHttpResponseProgressInfo', retData);
      if (done || retData.hasOwnProperty('ok')) {
        //结束
        return onFinished(retData);
      } else {
        //进行中
        const { progressValue, progressText } = retData;
        if (progressValue && setMergeProgressInfo)
          setMergeProgressInfo({ progressValue, progressText });
      }
      // 继续读取下一个数据块
      return readChunk();
    });
  }
  return readChunk();
}

/**
 * 设置输出进度
 * @param response
 * @param setMergeProgressInfo
 * @returns
 */
export function updateImportProgressInfo(
  response: Response,
  setMergeProgressInfo: any,
  onFinished?: (retData: API.SerResult) => void,
) {
  return updateHttpResponseProgressInfo(
    response,
    setMergeProgressInfo,
    (retData: API.SerResult) => {
      if (retData.ok) {
        setMergeProgressInfo({ progressValue: 100, progressText: '' });
        msgInfo(`导入完成,耗时 ${retData.data.takeSeconds} 秒`);
      } else {
        msgError(retData.data);
      }
      onFinished && onFinished(retData);
    },
  );
}
